package com.joinval.common.excel;


import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelReader {



    public static void main(String[] args) throws Exception {

        //创建文件
        //File file = new File("C:/Users/admin/Desktop/绿色认证/认证计划表-样版2.0.xlsx");
        File file = new File("C:/Users/admin/Desktop/绿色认证/仅供参考模板4.0.xls");
        //创建流
        InputStream is = new FileInputStream(file);
        List<List<String>> list = ExcelReader.readExcel(is);

        for (List<String> l : list) {

            System.out.println(l.get(0)+"---"+l.get(3)+"---"+l.get(4)+"---"+l.get(5)+"---"+l.get(16));

        }
    }




    /**
     * Excel读取 操作
     */
    public static List<List<String>> readExcel(InputStream is)
            throws IOException {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat df = new DecimalFormat("000.000000");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式

        /** 得到第二个sheet */
        Sheet sheet = wb.getSheetAt(1);

        //String sheetName = wb.getSheetAt(0).getSheetName();
        //Cell cellName = sheet.getRow(1).getCell(0);

        //System.out.println(sheetName);
        //System.out.println(cellName);

        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        System.out.println(totalRows);

    /*    *//** 得到Excel的列数 *//*
        int count = 0;
        int columnTotalNum =0;
        for (Row row : sheet) {
            // 跳过第一和第二行的目录
            *//*if(count < 2 ) {
                count++;
                continue;
            }*//*
            //如果当前行没有数据，跳出循环
            *//*if(row.getCell(0).toString().equals("")){
                break;
            }*//*
            //获取总列数(空格的不计算)
            columnTotalNum = row.getPhysicalNumberOfCells();
            //System.out.println("总列数：" + columnTotalNum);
            //System.out.println("最大列数：" + row.getLastCellNum());
        }*/



        /** 得到Excel的列数 */
  /*      int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }*/

        List<List<String>> dataLst = new ArrayList<List<String>>();

        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {

                    //HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    //cellValue= hSSFDataFormatter.formatCellValue(cell);


                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC: // 数字
                        /*cellValue = cell.getNumericCellValue() + "";
                        break;*/
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()); //日期型
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                String temp = cell.getStringCellValue();
                                // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                                if (temp.indexOf(".") > -1) {
                                    cellValue  = String.valueOf(new Double(temp)).trim();
                                    //cellValue = df.format(cell.getDateCellValue());
                                } else {
                                    cellValue  = temp.trim();
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空值
                            cellValue = "";
                            break;
                        case Cell.CELL_TYPE_ERROR: // 故障
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                //rowLst.add(sheetName);
                //rowLst.add(cellName+"");
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }


    /**
     * Excel读取 操作
     */
    public static List<List<String>> readExcelNew(InputStream is)
            throws IOException {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat df = new DecimalFormat("000.000000");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式

        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(0);

        //String sheetName = wb.getSheetAt(0).getSheetName();
        //Cell cellName = sheet.getRow(1).getCell(0);

        //System.out.println(sheetName);
        //System.out.println(cellName);

        /** 得到Excel的行数 */
        int totalRows = sheet.getLastRowNum();
        System.out.println("有"+totalRows+"行");

        /*    *//** 得到Excel的列数 *//*
        int count = 0;
        int columnTotalNum =0;
        for (Row row : sheet) {
            // 跳过第一和第二行的目录
            *//*if(count < 2 ) {
                count++;
                continue;
            }*//*
            //如果当前行没有数据，跳出循环
            *//*if(row.getCell(0).toString().equals("")){
                break;
            }*//*
            //获取总列数(空格的不计算)
            columnTotalNum = row.getPhysicalNumberOfCells();
            //System.out.println("总列数：" + columnTotalNum);
            //System.out.println("最大列数：" + row.getLastCellNum());
        }*/



        /** 得到Excel的列数 */
  /*      int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }*/

        List<List<String>> dataLst = new ArrayList<List<String>>();

        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 得到Excel的列数 */
            int cellRows = 11;
            /** 循环Excel的列 */
            for (int c = 0; c <cellRows; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {

                    //HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    //cellValue= hSSFDataFormatter.formatCellValue(cell);


                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC: // 数字
                        /*cellValue = cell.getNumericCellValue() + "";
                        break;*/
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()); //日期型
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                String temp = cell.getStringCellValue();
                                // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                                if (temp.indexOf(".") > -1) {
                                    cellValue  = String.valueOf(new Double(temp)).trim();
                                    //cellValue = df.format(cell.getDateCellValue());
                                } else {
                                    cellValue  = temp.trim();
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空值
                            cellValue = "";
                            break;
                        case Cell.CELL_TYPE_ERROR: // 故障
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                //rowLst.add(sheetName);
                //rowLst.add(cellName+"");
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }



}
